package cz.gillan.housecosts.sql;

import java.util.GregorianCalendar;

import android.content.Context;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;

public class DatabaseHelper extends SQLiteOpenHelper {
	private static final String DB_NAME = "HouseCosts";
	private static final int DB_VERSION = 2;
	private static final String DB_CREATE[] = new String[]{ "" +
			"CREATE TABLE " + GaugeDbAdapter.DB_TABLE + " (" +
			"_id INTEGER PRIMARY KEY AUTOINCREMENT, " +
			"type INTEGER," +
			"name TEXT NOT NULL," +
			"serialNumber TEXT," +
			"comment TEXT," +
			"magnitude TEXT" +
			");",
			"CREATE TABLE  " + StateDbAdapter.DB_TABLE + "  (" +
			"_id INTEGER PRIMARY KEY AUTOINCREMENT," +
			"gaugeid INTEGER NOT NULL," +
			"state REAL," +
			"date INTEGER," +
			"comment TEXT," +
			"FOREIGN KEY (gaugeid) REFERENCES gauge(_id)" +
			");"};
	private static final String DB_UPDATE[] = new String[] {"" +
			"DROP TABLE IF EXISTS " + GaugeDbAdapter.DB_TABLE + " ;", 
			"DROP TABLE IF EXISTS " + StateDbAdapter.DB_TABLE + ";"};
	
	private static final String[] DB_TEST_DATA = new String[] { 
		"INSERT INTO gauge (type, name, magnitude) VALUES (1, \"Elektrina\", \"kW/h\")",
		"INSERT INTO gauge (type, name, magnitude) VALUES (2, \"Studena voda\", \"m^3\")",
		"INSERT INTO gauge (type, name, magnitude) VALUES (3, \"Tepla voda\", \"m^3\")",
		

		"INSERT INTO state (date, state, gaugeId) VALUES (" + (new GregorianCalendar(2008,7,1).getTimeInMillis() / 1000l) + ", 21971, 1)",
		"INSERT INTO state (date, state, gaugeId) VALUES (" + (new GregorianCalendar(2008,7,1).getTimeInMillis() / 1000l) + ", 111.15, 2)",
		"INSERT INTO state (date, state, gaugeId) VALUES (" + (new GregorianCalendar(2008,7,1).getTimeInMillis() / 1000l) + ", 69.798, 3)",
		"INSERT INTO state (date, state, gaugeId) VALUES (" + (new GregorianCalendar(2008,8,1).getTimeInMillis() / 1000l) + ", 21992, 1)",
		"INSERT INTO state (date, state, gaugeId) VALUES (" + (new GregorianCalendar(2008,8,1).getTimeInMillis() / 1000l) + ", 111.99, 2)",
		"INSERT INTO state (date, state, gaugeId) VALUES (" + (new GregorianCalendar(2008,8,1).getTimeInMillis() / 1000l) + ", 69.798, 3)",
		"INSERT INTO state (date, state, gaugeId) VALUES (" + (new GregorianCalendar(2008,9,1).getTimeInMillis() / 1000l) + ", 22015, 1)",
		"INSERT INTO state (date, state, gaugeId) VALUES (" + (new GregorianCalendar(2008,9,1).getTimeInMillis() / 1000l) + ", 112.532, 2)",
		"INSERT INTO state (date, state, gaugeId) VALUES (" + (new GregorianCalendar(2008,9,1).getTimeInMillis() / 1000l) + ", 69.977, 3)",
		"INSERT INTO state (date, state, gaugeId) VALUES (" + (new GregorianCalendar(2008,10,4).getTimeInMillis() / 1000l) + ", 22106, 1)",
		"INSERT INTO state (date, state, gaugeId) VALUES (" + (new GregorianCalendar(2008,10,4).getTimeInMillis() / 1000l) + ", 114.535, 2)",
		"INSERT INTO state (date, state, gaugeId) VALUES (" + (new GregorianCalendar(2008,10,4).getTimeInMillis() / 1000l) + ", 71.1, 3)",
		"INSERT INTO state (date, state, gaugeId) VALUES (" + (new GregorianCalendar(2008,11,1).getTimeInMillis() / 1000l) + ", 22218, 1)",
		"INSERT INTO state (date, state, gaugeId) VALUES (" + (new GregorianCalendar(2008,11,1).getTimeInMillis() / 1000l) + ", 115.9, 2)",
		"INSERT INTO state (date, state, gaugeId) VALUES (" + (new GregorianCalendar(2008,11,1).getTimeInMillis() / 1000l) + ", 71.9, 3)",
		"INSERT INTO state (date, state, gaugeId) VALUES (" + (new GregorianCalendar(2008,12,1).getTimeInMillis() / 1000l) + ", 22347, 1)",
		"INSERT INTO state (date, state, gaugeId) VALUES (" + (new GregorianCalendar(2008,12,1).getTimeInMillis() / 1000l) + ", 117.56, 2)",
		"INSERT INTO state (date, state, gaugeId) VALUES (" + (new GregorianCalendar(2008,12,1).getTimeInMillis() / 1000l) + ", 72.95, 3)",
		"INSERT INTO state (date, state, gaugeId) VALUES (" + (new GregorianCalendar(2009,1,1).getTimeInMillis() / 1000l) + ", 22462, 1)",
		"INSERT INTO state (date, state, gaugeId) VALUES (" + (new GregorianCalendar(2009,1,1).getTimeInMillis() / 1000l) + ", 119.39, 2)",
		"INSERT INTO state (date, state, gaugeId) VALUES (" + (new GregorianCalendar(2009,1,1).getTimeInMillis() / 1000l) + ", 73.4, 3)",
		"INSERT INTO state (date, state, gaugeId) VALUES (" + (new GregorianCalendar(2009,2,1).getTimeInMillis() / 1000l) + ", 22594, 1)",
		"INSERT INTO state (date, state, gaugeId) VALUES (" + (new GregorianCalendar(2009,2,1).getTimeInMillis() / 1000l) + ", 121.12, 2)",
		"INSERT INTO state (date, state, gaugeId) VALUES (" + (new GregorianCalendar(2009,2,1).getTimeInMillis() / 1000l) + ", 74.3, 3)",
		"INSERT INTO state (date, state, gaugeId) VALUES (" + (new GregorianCalendar(2009,3,1).getTimeInMillis() / 1000l) + ", 22665, 1)",
		"INSERT INTO state (date, state, gaugeId) VALUES (" + (new GregorianCalendar(2009,3,1).getTimeInMillis() / 1000l) + ", 122.51, 2)",
		"INSERT INTO state (date, state, gaugeId) VALUES (" + (new GregorianCalendar(2009,3,1).getTimeInMillis() / 1000l) + ", 74.7, 3)",
		"INSERT INTO state (date, state, gaugeId) VALUES (" + (new GregorianCalendar(2009,4,1).getTimeInMillis() / 1000l) + ", 22786, 1)",
		"INSERT INTO state (date, state, gaugeId) VALUES (" + (new GregorianCalendar(2009,4,1).getTimeInMillis() / 1000l) + ", 125.28, 2)",
		"INSERT INTO state (date, state, gaugeId) VALUES (" + (new GregorianCalendar(2009,4,1).getTimeInMillis() / 1000l) + ", 75.644, 3)",
		"INSERT INTO state (date, state, gaugeId) VALUES (" + (new GregorianCalendar(2009,5,1).getTimeInMillis() / 1000l) + ", 22882, 1)",
		"INSERT INTO state (date, state, gaugeId) VALUES (" + (new GregorianCalendar(2009,5,1).getTimeInMillis() / 1000l) + ", 127.056, 2)",
		"INSERT INTO state (date, state, gaugeId) VALUES (" + (new GregorianCalendar(2009,5,1).getTimeInMillis() / 1000l) + ", 76.3, 3)",
		"INSERT INTO state (date, state, gaugeId) VALUES (" + (new GregorianCalendar(2009,6,1).getTimeInMillis() / 1000l) + ", 23015, 1)",
		"INSERT INTO state (date, state, gaugeId) VALUES (" + (new GregorianCalendar(2009,6,1).getTimeInMillis() / 1000l) + ", 128.201, 2)",
		"INSERT INTO state (date, state, gaugeId) VALUES (" + (new GregorianCalendar(2009,6,1).getTimeInMillis() / 1000l) + ", 76.82, 3)",
		"INSERT INTO state (date, state, gaugeId) VALUES (" + (new GregorianCalendar(2009,7,1).getTimeInMillis() / 1000l) + ", 23147, 1)",
		"INSERT INTO state (date, state, gaugeId) VALUES (" + (new GregorianCalendar(2009,7,1).getTimeInMillis() / 1000l) + ", 129.7, 2)",
		"INSERT INTO state (date, state, gaugeId) VALUES (" + (new GregorianCalendar(2009,7,1).getTimeInMillis() / 1000l) + ", 77.77, 3)",
		"INSERT INTO state (date, state, gaugeId) VALUES (" + (new GregorianCalendar(2009,8,1).getTimeInMillis() / 1000l) + ", 23311, 1)",
		"INSERT INTO state (date, state, gaugeId) VALUES (" + (new GregorianCalendar(2009,8,1).getTimeInMillis() / 1000l) + ", 132.5, 2)",
		"INSERT INTO state (date, state, gaugeId) VALUES (" + (new GregorianCalendar(2009,8,1).getTimeInMillis() / 1000l) + ", 78.52, 3)",
		"INSERT INTO state (date, state, gaugeId) VALUES (" + (new GregorianCalendar(2009,9,1).getTimeInMillis() / 1000l) + ", 23515, 1)",
		"INSERT INTO state (date, state, gaugeId) VALUES (" + (new GregorianCalendar(2009,9,1).getTimeInMillis() / 1000l) + ", 135.12, 2)",
		"INSERT INTO state (date, state, gaugeId) VALUES (" + (new GregorianCalendar(2009,9,1).getTimeInMillis() / 1000l) + ", 79.22, 3)",
		"INSERT INTO state (date, state, gaugeId) VALUES (" + (new GregorianCalendar(2009,10,1).getTimeInMillis() / 1000l) + ", 23676, 1)",
		"INSERT INTO state (date, state, gaugeId) VALUES (" + (new GregorianCalendar(2009,10,1).getTimeInMillis() / 1000l) + ", 136.93, 2)",
		"INSERT INTO state (date, state, gaugeId) VALUES (" + (new GregorianCalendar(2009,10,1).getTimeInMillis() / 1000l) + ", 79.91, 3)",
		"INSERT INTO state (date, state, gaugeId) VALUES (" + (new GregorianCalendar(2009,11,1).getTimeInMillis() / 1000l) + ", 23886, 1)",
		"INSERT INTO state (date, state, gaugeId) VALUES (" + (new GregorianCalendar(2009,11,1).getTimeInMillis() / 1000l) + ", 139.61, 2)",
		"INSERT INTO state (date, state, gaugeId) VALUES (" + (new GregorianCalendar(2009,11,1).getTimeInMillis() / 1000l) + ", 80.68, 3)",
		"INSERT INTO state (date, state, gaugeId) VALUES (" + (new GregorianCalendar(2009,12,1).getTimeInMillis() / 1000l) + ", 24091, 1)",
		"INSERT INTO state (date, state, gaugeId) VALUES (" + (new GregorianCalendar(2009,12,1).getTimeInMillis() / 1000l) + ", 142.07, 2)",
		"INSERT INTO state (date, state, gaugeId) VALUES (" + (new GregorianCalendar(2009,12,1).getTimeInMillis() / 1000l) + ", 81.45, 3)",
		"INSERT INTO state (date, state, gaugeId) VALUES (" + (new GregorianCalendar(2010,1,1).getTimeInMillis() / 1000l) + ", 24232, 1)",
		"INSERT INTO state (date, state, gaugeId) VALUES (" + (new GregorianCalendar(2010,1,1).getTimeInMillis() / 1000l) + ", 143.35, 2)",
		"INSERT INTO state (date, state, gaugeId) VALUES (" + (new GregorianCalendar(2010,1,1).getTimeInMillis() / 1000l) + ", 81.86, 3)",
		"INSERT INTO state (date, state, gaugeId) VALUES (" + (new GregorianCalendar(2010,2,1).getTimeInMillis() / 1000l) + ", 24382, 1)",
		"INSERT INTO state (date, state, gaugeId) VALUES (" + (new GregorianCalendar(2010,2,1).getTimeInMillis() / 1000l) + ", 146.36, 2)",
		"INSERT INTO state (date, state, gaugeId) VALUES (" + (new GregorianCalendar(2010,2,1).getTimeInMillis() / 1000l) + ", 83.25, 3)",
		"INSERT INTO state (date, state, gaugeId) VALUES (" + (new GregorianCalendar(2010,3,1).getTimeInMillis() / 1000l) + ", 24503, 1)",
		"INSERT INTO state (date, state, gaugeId) VALUES (" + (new GregorianCalendar(2010,3,1).getTimeInMillis() / 1000l) + ", 148.2, 2)",
		"INSERT INTO state (date, state, gaugeId) VALUES (" + (new GregorianCalendar(2010,3,1).getTimeInMillis() / 1000l) + ", 84.31, 3)",
		"INSERT INTO state (date, state, gaugeId) VALUES (" + (new GregorianCalendar(2010,4,1).getTimeInMillis() / 1000l) + ", 24671, 1)",
		"INSERT INTO state (date, state, gaugeId) VALUES (" + (new GregorianCalendar(2010,4,1).getTimeInMillis() / 1000l) + ", 150.11, 2)",
		"INSERT INTO state (date, state, gaugeId) VALUES (" + (new GregorianCalendar(2010,4,1).getTimeInMillis() / 1000l) + ", 85.83, 3)",
		"INSERT INTO state (date, state, gaugeId) VALUES (" + (new GregorianCalendar(2010,5,1).getTimeInMillis() / 1000l) + ", 24793, 1)",
		"INSERT INTO state (date, state, gaugeId) VALUES (" + (new GregorianCalendar(2010,5,1).getTimeInMillis() / 1000l) + ", 151.87, 2)",
		"INSERT INTO state (date, state, gaugeId) VALUES (" + (new GregorianCalendar(2010,5,1).getTimeInMillis() / 1000l) + ", 86.99, 3)",
		"INSERT INTO state (date, state, gaugeId) VALUES (" + (new GregorianCalendar(2010,6,1).getTimeInMillis() / 1000l) + ", 24941, 1)",
		"INSERT INTO state (date, state, gaugeId) VALUES (" + (new GregorianCalendar(2010,6,1).getTimeInMillis() / 1000l) + ", 154.15, 2)",
		"INSERT INTO state (date, state, gaugeId) VALUES (" + (new GregorianCalendar(2010,6,1).getTimeInMillis() / 1000l) + ", 88.91, 3)",
		"INSERT INTO state (date, state, gaugeId) VALUES (" + (new GregorianCalendar(2010,7,1).getTimeInMillis() / 1000l) + ", 25035, 1)",
		"INSERT INTO state (date, state, gaugeId) VALUES (" + (new GregorianCalendar(2010,7,1).getTimeInMillis() / 1000l) + ", 156.37, 2)",
		"INSERT INTO state (date, state, gaugeId) VALUES (" + (new GregorianCalendar(2010,7,1).getTimeInMillis() / 1000l) + ", 90.25, 3)",
		"INSERT INTO state (date, state, gaugeId) VALUES (" + (new GregorianCalendar(2010,8,1).getTimeInMillis() / 1000l) + ", 25147, 1)",
		"INSERT INTO state (date, state, gaugeId) VALUES (" + (new GregorianCalendar(2010,8,1).getTimeInMillis() / 1000l) + ", 158.99, 2)",
		"INSERT INTO state (date, state, gaugeId) VALUES (" + (new GregorianCalendar(2010,8,1).getTimeInMillis() / 1000l) + ", 91.51, 3)",
		"INSERT INTO state (date, state, gaugeId) VALUES (" + (new GregorianCalendar(2010,9,1).getTimeInMillis() / 1000l) + ", 25238, 1)",
		"INSERT INTO state (date, state, gaugeId) VALUES (" + (new GregorianCalendar(2010,9,1).getTimeInMillis() / 1000l) + ", 160.76, 2)",
		"INSERT INTO state (date, state, gaugeId) VALUES (" + (new GregorianCalendar(2010,9,1).getTimeInMillis() / 1000l) + ", 92.39, 3)",
		"INSERT INTO state (date, state, gaugeId) VALUES (" + (new GregorianCalendar(2010,10,1).getTimeInMillis() / 1000l) + ", 25327, 1)",
		"INSERT INTO state (date, state, gaugeId) VALUES (" + (new GregorianCalendar(2010,10,1).getTimeInMillis() / 1000l) + ", 162.15, 2)",
		"INSERT INTO state (date, state, gaugeId) VALUES (" + (new GregorianCalendar(2010,10,1).getTimeInMillis() / 1000l) + ", 93.7, 3)",
		"INSERT INTO state (date, state, gaugeId) VALUES (" + (new GregorianCalendar(2010,11,01).getTimeInMillis() / 1000l) + ", 25442, 1)",
		"INSERT INTO state (date, state, gaugeId) VALUES (" + (new GregorianCalendar(2010,11,01).getTimeInMillis() / 1000l) + ", 164.2, 2)",
		"INSERT INTO state (date, state, gaugeId) VALUES (" + (new GregorianCalendar(2010,11,01).getTimeInMillis() / 1000l) + ", 95.2, 3)",
		"INSERT INTO state (date, state, gaugeId) VALUES (" + (new GregorianCalendar(2010,12,01).getTimeInMillis() / 1000l) + ", 25567, 1)",
		"INSERT INTO state (date, state, gaugeId) VALUES (" + (new GregorianCalendar(2010,12,01).getTimeInMillis() / 1000l) + ", 166.77, 2)",
		"INSERT INTO state (date, state, gaugeId) VALUES (" + (new GregorianCalendar(2010,12,01).getTimeInMillis() / 1000l) + ", 97, 3)",
		"INSERT INTO state (date, state, gaugeId) VALUES (" + (new GregorianCalendar(2011,1,1).getTimeInMillis() / 1000l) + ", 25661, 1)",
		"INSERT INTO state (date, state, gaugeId) VALUES (" + (new GregorianCalendar(2011,1,1).getTimeInMillis() / 1000l) + ", 168.39, 2)",
		"INSERT INTO state (date, state, gaugeId) VALUES (" + (new GregorianCalendar(2011,1,1).getTimeInMillis() / 1000l) + ", 98.2, 3)",
		"INSERT INTO state (date, state, gaugeId) VALUES (" + (new GregorianCalendar(2011,2,1).getTimeInMillis() / 1000l) + ", 25787, 1)",
		"INSERT INTO state (date, state, gaugeId) VALUES (" + (new GregorianCalendar(2011,2,1).getTimeInMillis() / 1000l) + ", 170.6, 2)",
		"INSERT INTO state (date, state, gaugeId) VALUES (" + (new GregorianCalendar(2011,2,1).getTimeInMillis() / 1000l) + ", 99.83, 3)",
		"INSERT INTO state (date, state, gaugeId) VALUES (" + (new GregorianCalendar(2011,3,1).getTimeInMillis() / 1000l) + ", 25884, 1)",
		"INSERT INTO state (date, state, gaugeId) VALUES (" + (new GregorianCalendar(2011,3,1).getTimeInMillis() / 1000l) + ", 172.1, 2)",
		"INSERT INTO state (date, state, gaugeId) VALUES (" + (new GregorianCalendar(2011,3,1).getTimeInMillis() / 1000l) + ", 101.08, 3)",
		"INSERT INTO state (date, state, gaugeId) VALUES (" + (new GregorianCalendar(2011,4,1).getTimeInMillis() / 1000l) + ", 25990, 1)",
		"INSERT INTO state (date, state, gaugeId) VALUES (" + (new GregorianCalendar(2011,4,1).getTimeInMillis() / 1000l) + ", 173.45, 2)",
		"INSERT INTO state (date, state, gaugeId) VALUES (" + (new GregorianCalendar(2011,4,1).getTimeInMillis() / 1000l) + ", 101.94, 3)",
		"INSERT INTO state (date, state, gaugeId) VALUES (" + (new GregorianCalendar(2011,5,1).getTimeInMillis() / 1000l) + ", 26096, 1)",
		"INSERT INTO state (date, state, gaugeId) VALUES (" + (new GregorianCalendar(2011,5,1).getTimeInMillis() / 1000l) + ", 175.13, 2)",
		"INSERT INTO state (date, state, gaugeId) VALUES (" + (new GregorianCalendar(2011,5,1).getTimeInMillis() / 1000l) + ", 103.03, 3)",
		"INSERT INTO state (date, state, gaugeId) VALUES (" + (new GregorianCalendar(2011,6,1).getTimeInMillis() / 1000l) + ", 26208, 1)",
		"INSERT INTO state (date, state, gaugeId) VALUES (" + (new GregorianCalendar(2011,6,1).getTimeInMillis() / 1000l) + ", 177.57, 2)",
		"INSERT INTO state (date, state, gaugeId) VALUES (" + (new GregorianCalendar(2011,6,1).getTimeInMillis() / 1000l) + ", 104.41, 3)",

	};

	public DatabaseHelper(Context context) {
		super(context, DB_NAME, null, DB_VERSION);	
	}
	
	//@Override
	public void onCreate(SQLiteDatabase database) {
		for (int i = 0; i < DB_CREATE.length; i++) {
			database.execSQL(DB_CREATE[i]);
		}
		
		createTestDate(database);
	}

	//@Override
	public void onUpgrade(SQLiteDatabase database, int oldVersion, int newVersion) {
		for (int i = 0; i < DB_UPDATE.length; i++) {
			database.execSQL(DB_UPDATE[i]);
		}
		onCreate(database);
	}

	private void createTestDate(SQLiteDatabase database) {
		for (int i = 0; i < DB_TEST_DATA.length; i++) {
			database.execSQL(DB_TEST_DATA[i]);
		}
	}
}
